package dao;

import entity.Column;
import util.DatabaseUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ColumnDao {
    public List<Column> searchColumnsByKeyword(String keyword) {
        String sql = "SELECT * FROM `column` WHERE name LIKE ?";
        List<Column> columns = new ArrayList<>();
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)) {
            pst.setString(1, "%" + keyword + "%");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                columns.add(mapResultSetToColumn(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return columns;
    }

    public List<Column> getColumnsByUserId(int userId) {
        List<Column> columns = new ArrayList<>();
        String sql = "SELECT * FROM `column` WHERE userId = ?";
        try (Connection connection = DatabaseUtil.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                columns.add(mapResultSetToColumn(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return columns;
    }

    public void createColumn(String name, int userId) {
        String sql = "INSERT INTO `column` (name, userId) VALUES (?, ?)";
        try (Connection connection =DatabaseUtil.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, name);
            stmt.setInt(2, userId);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 将博文加入专栏
    public boolean addBlogToColumn(int blogId, int columnId) {
        String sql = "UPDATE blog SET columnId = ? WHERE id = ?";
        try (Connection connection = DatabaseUtil.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, columnId);
            stmt.setInt(2, blogId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 将博文移出专栏
    public boolean removeBlogFromColumn(int blogId) {
        String sql = "UPDATE blog SET columnId = NULL WHERE id = ?";
        try (Connection connection = DatabaseUtil.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, blogId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean deleteColumn(int columnId) {
        // 在删除专栏之前，确保将博文移出该专栏
        String updateSql = "UPDATE blog SET columnId = NULL WHERE columnId = ?";
        String deleteSql = "DELETE FROM `column` WHERE id = ?";

        try (Connection connection = DatabaseUtil.getConnection()) {
            // 先将博文移出专栏
            try (PreparedStatement updateStmt = connection.prepareStatement(updateSql)) {
                updateStmt.setInt(1, columnId);
                updateStmt.executeUpdate();
            }

            // 然后删除专栏
            try (PreparedStatement deleteStmt = connection.prepareStatement(deleteSql)) {
                deleteStmt.setInt(1, columnId);
                int affectedRows = deleteStmt.executeUpdate();
                return affectedRows > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private Column mapResultSetToColumn(ResultSet rs) throws SQLException {
        return new Column(
                rs.getInt("id"),
                rs.getInt("userId"),
                rs.getString("name"),
                rs.getTimestamp("createdTime")
        );
    }
}

